package mysale.dao.impl;

import mysale.dao.CartDao;
import mysale.pojo.Cart;
import mysale.pojo.Food;
import mysale.util.JDBCUtil;
import java.text.SimpleDateFormat;

import javax.sound.midi.Soundbank;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author SnakeWoodMan
 * @create 2020-12-29 17:59
 */
public class CartDaoImpl implements CartDao {
    private Connection con;
    public CartDaoImpl(Connection con){
        this.con = con;
    }

    @Override
    public List<Cart> getAllcartinfo(String uid) {
        String sql = "SELECT " +
                "cart.uid,cart.fid,food.foodname,food.pic,food.foodinfo," +
                "food.flavor,food.price,food.stock,cart.number,food.unit," +
                "cart.isbuy,cart.buytime " +
                "FROM cart,food,user " +
                "WHERE user.uid= ? AND " +
                "cart.uid = user.uid AND " +
                "cart.fid = food.fid";
        con = JDBCUtil.getConnection();
        List<Cart> cartList = new ArrayList<Cart>();
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        Cart cart = null;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, uid);
            rs =  pstmt.executeQuery();//rs存放sql查询结果
            while (rs.next()){
                cart = new Cart();
                cart.setCuid(rs.getString("uid"));
                cart.setCfid(rs.getString("fid"));
                cart.setCpic(rs.getString("pic"));
                cart.setCfname(rs.getString("foodname"));
                cart.setCflavor(rs.getString("flavor"));
                cart.setCprice(rs.getString("price"));
                cart.setCunit(rs.getString("unit"));
                cart.setNumber(rs.getString("number"));
                cart.setIsbuy(rs.getBoolean("isbuy"));
                cart.setBuytime(rs.getTimestamp("buytime"));
                cart.setStock(rs.getString("stock"));
                cart.setCfinfo(rs.getString("foodinfo"));
                cartList.add(cart);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            try{
//                rs.close();
//            } catch (SQLException e){
//                e.printStackTrace();
//            }
            try {
                pstmt.close();
            } catch (SQLException e){
                e.printStackTrace();
            }

        }
        return cartList;
    }

    @Override
    public List<Cart> getbuycartinfo(String uid) {
        String sql = "SELECT " +
                "cart.uid,cart.fid,food.foodname,food.pic,food.foodinfo," +
                "food.flavor,food.price,food.stock,cart.number,food.unit," +
                "cart.isbuy,cart.buytime " +
                "FROM cart,food,user " +
                "WHERE user.uid= ? AND " +
                "cart.uid = user.uid AND " +
                "cart.fid = food.fid AND cart.isbuy = 1";
        con = JDBCUtil.getConnection();
        List<Cart> cartList = new ArrayList<Cart>();
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        Cart cart = null;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, uid);
            rs =  pstmt.executeQuery();//rs存放sql查询结果
            while (rs.next()){
                cart = new Cart();
                cart.setCuid(rs.getString("uid"));
                cart.setCfid(rs.getString("fid"));
                cart.setCpic(rs.getString("pic"));
                cart.setCfname(rs.getString("foodname"));
                cart.setCflavor(rs.getString("flavor"));
                cart.setCprice(rs.getString("price"));
                cart.setCunit(rs.getString("unit"));
                cart.setNumber(rs.getString("number"));
                cart.setIsbuy(rs.getBoolean("isbuy"));
                cart.setBuytime(rs.getTimestamp("buytime"));
                cart.setStock(rs.getString("stock"));
                cart.setCfinfo(rs.getString("foodinfo"));
                cartList.add(cart);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            try{
//                rs.close();
//            } catch (SQLException e){
//                e.printStackTrace();
//            }
            try {
                pstmt.close();
            } catch (SQLException e){
                e.printStackTrace();
            }

        }
        return cartList;
    }

    @Override
    public List<Cart> getnotbuycartinfo(String uid) {
        String sql = "SELECT " +
                "cart.uid,cart.fid,food.foodname,food.pic,food.foodinfo," +
                "food.flavor,food.price,food.stock,cart.number,food.unit," +
                "cart.isbuy,cart.buytime " +
                "FROM cart,food,user " +
                "WHERE user.uid= ? AND " +
                "cart.uid = user.uid AND " +
                "cart.fid = food.fid and cart.isbuy = 0";
        con = JDBCUtil.getConnection();
        List<Cart> cartList = new ArrayList<Cart>();
        PreparedStatement pstmt = null;
        ResultSet rs =null;
        Cart cart = null;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, uid);
//            System.out.println(sql);
            rs =  pstmt.executeQuery();//rs存放sql查询结果
            while (rs.next()){
                cart = new Cart();
                cart.setCuid(rs.getString("uid"));
                cart.setCfid(rs.getString("fid"));
                cart.setCpic(rs.getString("pic"));
                cart.setCfname(rs.getString("foodname"));
                cart.setCflavor(rs.getString("flavor"));
                cart.setCprice(rs.getString("price"));
                cart.setCunit(rs.getString("unit"));
                cart.setNumber(rs.getString("number"));
                cart.setIsbuy(rs.getBoolean("isbuy"));
                cart.setBuytime(rs.getTimestamp("buytime"));
                cart.setStock(rs.getString("stock"));
                cart.setCfinfo(rs.getString("foodinfo"));
                cartList.add(cart);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
//            try{
//                rs.close();
//            } catch (SQLException e){
//                e.printStackTrace();
//            }
            try {
                pstmt.close();
            } catch (SQLException e){
                e.printStackTrace();
            }

        }
        return cartList;
    }

    @Override
    public List<Cart> getCartbyuidfid(Cart cart) {
        String sql = "select * FROM cart where uid=? and fid=? and isbuy=0";
        List<Cart> cartList = new ArrayList<Cart>();
        con = JDBCUtil.getConnection();
        PreparedStatement pstmt = null;
        boolean result = false;
        try {
            ResultSet rs =null;
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getCuid());
            pstmt.setString(2, cart.getCfid());
            rs =  pstmt.executeQuery();//rs存放sql查询结果
            while (rs.next()){
                cart = new Cart();
                cart.setCuid(rs.getString("uid"));
                cart.setCfid(rs.getString("fid"));
                cart.setIsbuy(rs.getBoolean("isbuy"));
                cart.setBuytime(rs.getTimestamp("buytime"));
                cartList.add(cart);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return cartList;
    }

    @Override
    public boolean insertCart(Cart cart) {
        String sql = "insert into cart values (?,?,?,0,null)";
        PreparedStatement pstmt = null;
        con = JDBCUtil.getConnection();
        boolean result = false;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getCuid());
            pstmt.setString(2, cart.getCfid());
//            System.out.println("cart.cuid:"+cart.getCuid());
//            System.out.println("cart.cfid:"+cart.getCfid());
//            System.out.println("cart.number:"+cart.getNumber());
            pstmt.setString(3, cart.getNumber());
            if(pstmt.executeUpdate()>0){
                result=true;
            }
//        } catch (SQLIntegrityConstraintViolationException e) {//捕获用户名相同的异常
//            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public boolean deleteCart(Cart cart) {
        String sql = "DELETE FROM cart where uid=? and fid=? and buytime=?";
        PreparedStatement pstmt = null;
        con = JDBCUtil.getConnection();
        boolean result = false;
        try {
            String gettedBuytime = String.valueOf(cart.getBuytime());
            System.out.println("isempty:"+gettedBuytime);
            System.out.println("isempty:"+gettedBuytime.equals("null"));
            if(gettedBuytime.equals("null")){
                sql = "DELETE FROM cart where uid=? and fid=? and buytime is null";
            }
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getCuid());
            pstmt.setString(2, cart.getCfid());
            if(!gettedBuytime.equals("null")){
                pstmt.setString(3, String.valueOf(cart.getBuytime()));
            }

//            System.out.println("cart.cuid:"+cart.getCuid());
//            System.out.println("cart.cfid:"+cart.getCfid());
//            System.out.println("购买时间:::"+String.valueOf(cart.getBuytime()));
//            System.out.println("::::::::"+cart.getBuytime());
            if(pstmt.executeUpdate()>0){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    /*               =未完成=                */
    public boolean updateCart(Cart cart) {
        String sql = "update cart set number = ? where uid=? and fid=? and isbuy=0";
        PreparedStatement pstmt = null;
        con = JDBCUtil.getConnection();
        boolean result = false;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getNumber());
            pstmt.setString(2, cart.getCuid());
            pstmt.setString(3, cart.getCfid());
            System.out.println("14cart.cuid:"+cart.getCuid());
            System.out.println("14cart.cfid:"+cart.getCfid());
            System.out.println("14cart.number:"+cart.getNumber());
            if(pstmt.executeUpdate()>0){
                result=true;
            }
//        } catch (SQLIntegrityConstraintViolationException e) {//捕获用户名相同的异常
//            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public boolean buyCart(Cart cart) {
        String sql = "update cart set isbuy=1,buytime=CURRENT_TIMESTAMP where uid=? and fid=? and isbuy=0";
        PreparedStatement pstmt = null;
        con = JDBCUtil.getConnection();
        boolean result = false;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getCuid());
            pstmt.setString(2, cart.getCfid());
//            System.out.println("cart.cuid:"+cart.getCuid());
//            System.out.println("cart.cfid:"+cart.getCfid());
            if(pstmt.executeUpdate()>0){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    @Override
    public boolean subtractCartStock(Cart cart) {
        String sql = "update food set stock=?-? where fid=?";
        PreparedStatement pstmt = null;
        con = JDBCUtil.getConnection();
        boolean result = false;
        try {
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, cart.getStock());
            pstmt.setString(2, cart.getNumber());
            pstmt.setString(3, cart.getCfid());
            String now_stock = cart.getStock();
            String now_number= cart.getNumber();
            int ns = Integer.parseInt(now_stock);//库存数
            int nn = Integer.parseInt(now_number);//需求数
            if (ns>nn){//库存大于需求数
                if(pstmt.executeUpdate()>0){
                    result=true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(pstmt!=null){
                try {
                    pstmt.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
}
